
# 个人数据——用例执行
from common.constant import TEST_DEPARTMENT_ID


def construct_person_case_execute_data_sql(
        _date_start_time,
        _date_end_time,
        _tester
):
    return f"""
# 用例执行个人数据
select
    usr.realname as tester,
    count(trest.id) as total,
    count(if(cas.type = 'feature', 1, null)) as feature,
    count(if(cas.type = 'SFYZ', 1, null)) as SFYZ,
    count(if(cas.type = 'XTXN', 1, null)) as XTXN,
    count(if(cas.type = 'DTYZ', 1, null)) as DTYZ,
    count(if(cas.type = 'PTYZ', 1, null)) as PTYZ,
    count(if(cas.type = 'performance', 1, null)) as performance,
    count(if(cas.type = 'config', 1, null)) as config,
    count(if(cas.type = 'install', 1, null)) as install,
    count(if(cas.type = 'security', 1, null)) as security,
    count(if(cas.type = 'interface', 1, null)) as interface,
    count(if(cas.type = 'unit', 1, null)) as unit,
    count(if(cas.type = 'other', 1, null)) as other
from
    zt_testresult as trest
left join
    zt_testrun as trun
on
    trun.id = trest.run
left join
    zt_case as cas
on
    cas.id=trun.`case`
LEFT JOIN
    zt_testtask as tsk 
on 
    tsk.id=trun.task 
LEFT JOIN
    zt_project as pj 
ON
    pj.id=tsk.project
LEFT JOIN
    zt_project as pject 
ON
    SUBSTRING_INDEX(SUBSTRING_INDEX(pj.path,',',2),',',-1)=pject.id
left join
    (
			select 
				usr.account as account, 
				usr.realname as realname
			from 
				zt_user as usr 
			LEFT JOIN
				zt_dept as dpt 
			on 
				dpt.id=usr.dept 
			WHERE
				usr.deleted = '0'
			and 
				SUBSTRING_INDEX(SUBSTRING_INDEX(dpt.path,',',-2),',',1) = {TEST_DEPARTMENT_ID}
		) as usr
on
    usr.account = trun.lastRunner
where
    trest.run > 0
and
    cas.deleted = '0'
    {
    '''AND
        trun.lastRunDate >= "%s" ''' % _date_start_time if _date_start_time else ""
    }
    {
    '''AND
        trun.lastRunDate < "%s" ''' % _date_end_time if _date_end_time else ""
    }
    {
    f'''AND
            usr.realname in  {_tester}''' if _tester and len(_tester) > 1 
    else f'''
        AND
            usr.realname = "{_tester[0]}"
    ''' if _tester
    else 
        ''
}
GROUP BY
		usr.account
"""




# 个人数据——用例新增
def construct_person_case_create_data_sql(
        _date_start_time,
        _date_end_time,
        _tester
):
    return f"""
select
            usr.realname                                    as tester,
            count(cas.id)                                   as total,
            count(if(cas.type = 'feature', 1, null))        as feature,
            count(if(cas.type = 'SFYZ', 1, null))           as SFYZ,
            count(if(cas.type = 'XTXN', 1, null))           as XTXN,
            count(if(cas.type = 'DTYZ', 1, null))           as DTYZ,
            count(if(cas.type = 'PTYZ', 1, null))           as PTYZ,
            count(if(cas.type = 'performance', 1, null))    as performance,
            count(if(cas.type = 'config', 1, null))         as config,
            count(if(cas.type = 'install', 1, null))        as install,
            count(if(cas.type = 'security', 1, null))       as security,
            count(if(cas.type = 'interface', 1, null))      as interface,
            count(if(cas.type = 'unit', 1, null))           as unit,
            count(if(cas.type = 'other', 1, null))          as other
    from 
        zt_case as cas
    left join
    (
			select 
				usr.account as account, 
				usr.realname as realname
			from 
				zt_user as usr 
			LEFT JOIN
				zt_dept as dpt 
			on 
				dpt.id=usr.dept 
			WHERE
				usr.deleted = '0'
			and 
				SUBSTRING_INDEX(SUBSTRING_INDEX(dpt.path,',',-2),',',1) = {TEST_DEPARTMENT_ID}
		) as usr
    on
        usr.account = cas.openedBy
     where 
        cas.deleted = '0'
    and
        cas.fromCaseId = 0
    {
    '''AND
        cas.openedDate >= "%s" ''' % _date_start_time if _date_start_time else ""
    }
    {
    '''AND
        cas.openedDate < "%s" ''' % _date_end_time if _date_end_time else ""
    }
    {
        f'''AND
                usr.realname in  {_tester}''' if _tester and len(_tester) > 1 
        else f'''
            AND
                usr.realname = "{_tester[0]}"
        ''' if _tester
        else 
            ''
    }
		group by 
				usr.account
"""


# 个人数据——Bug新增
def construct_person_Bug_create_data_sql(
        _date_start_time,
        _date_end_time,
        _tester
):
    return f"""
        select
            usr.realname as tester, 
			count(bug.id) as total
        FROM
            zt_bug as bug 
        LEFT JOIN
            zt_project as pj 
        ON
            pj.id=bug.project
        LEFT JOIN
            zt_project as pject 
        ON
            pject.id=SUBSTRING_INDEX(SUBSTRING_INDEX(pj.path,",",2),",",-1) 
        LEFT JOIN
                (
                    select 
                        usr.account as account, 
                        usr.realname as realname
                    from 
                        zt_user as usr 
                    LEFT JOIN
                        zt_dept as dpt 
                    on 
                        dpt.id=usr.dept 
                    WHERE
                        usr.deleted = '0'
                    and 
                        SUBSTRING_INDEX(SUBSTRING_INDEX(dpt.path,',',-2),',',1) = {TEST_DEPARTMENT_ID}
                ) as usr
        ON
            usr.account=bug.openedBy 
        WHERE
            bug.deleted="0" 
        AND
            usr.realname is not NULL
--         and 
--             pject.status != 'closed'
        {
        '''AND
            bug.openedDate >= "%s" ''' % _date_start_time if _date_start_time else ""
        }
        {
        '''AND
            bug.openedDate < "%s" ''' % _date_end_time if _date_end_time else ""
        }
        {
            f'''AND
                    usr.realname in  {_tester}''' if _tester and len(_tester) > 1 
            else f'''
                AND
                    usr.realname = "{_tester[0]}"
            ''' if _tester
            else 
                ''
        }
        GROUP BY
            bug.openedBy
"""


# 个人数据——Bug验证
def construct_person_Bug_verify_data_sql(
        _date_start_time,
        _date_end_time,
        _tester
):
    return f"""
select
        if(usr.realname is not null,usr.realname, usr.account)  as tester,
        count(distinct bug.id) as total
--         bug.id,
--         Date(act.date)
    from
        zt_action as act
    left join
        zt_bug as bug
    on
        bug.id=act.objectID
    left join
        (
            select 
                usr.account as account, 
                usr.realname as realname
            from 
                zt_user as usr 
            LEFT JOIN
                zt_dept as dpt 
            on 
                dpt.id=usr.dept 
            WHERE
                usr.deleted = '0'
            and 
                SUBSTRING_INDEX(SUBSTRING_INDEX(dpt.path,',',-2),',',1) = {TEST_DEPARTMENT_ID}
        ) as usr
    on
        usr.account=act.actor
    # LEFT JOIN
    #     zt_project as pj 
    # ON
    #     pj.id=act.execution
    where
        act.action='edited'
    and
        objectType='bug'
    # and
    #    bug.status = 'active'
    # and 
    #     pj.status = 'doing'
    and
        act.comment != ''
    and
        bug.openedBy = act.actor
{
    '''AND
	        act.date >= '%s' ''' % _date_start_time if _date_start_time else ''
}
{
    '''AND
	        act.date < '%s' ''' % _date_end_time if _date_end_time else ''
}
{
    f'''AND
            usr.realname in  {_tester}''' if _tester and len(_tester) > 1 
    else f'''
        AND
            usr.realname = "{_tester[0]}"
    ''' if _tester
    else 
        ''
}
    group by
				usr.account
"""



# 个人数据——Bug关闭
def construct_person_Bug_close_data_sql(
        _date_start_time,
        _date_end_time,
        _tester
):
    return f"""
SELECT
        usr.realname as tester,
        count(bug.id) as total
    FROM
        zt_bug as bug 
    LEFT JOIN
        zt_project as pj 
    ON
        pj.id = bug.project 
    LEFT JOIN
        zt_project as pject 
    ON
        pject.id=SUBSTRING_INDEX(SUBSTRING_INDEX(pj.path, ',', 2),',', -1)
    LEFT JOIN
				(
					select 
						usr.account as account, 
						usr.realname as realname
					from 
						zt_user as usr 
					LEFT JOIN
						zt_dept as dpt 
					on 
						dpt.id=usr.dept 
					WHERE
						usr.deleted = '0'
					and 
						SUBSTRING_INDEX(SUBSTRING_INDEX(dpt.path,',',-2),',',1) = {TEST_DEPARTMENT_ID}
				) as usr
    ON
        usr.account = bug.closedBy
    WHERE
        bug.deleted='0'
    and 
        bug.status='closed'
{
    '''AND
	        bug.closedDate >= '%s' ''' % _date_start_time if _date_start_time else ''
}
{
    '''AND
	        bug.closedDate < '%s' ''' % _date_end_time if _date_end_time else ''
}
{
    f'''AND
            usr.realname in  {_tester}''' if _tester and len(_tester) > 1 
    else f'''
        AND
            usr.realname = "{_tester[0]}"
    ''' if _tester
    else 
        ''
}
    GROUP BY
				usr.account
"""


# 个人数据——Bug激活
def construct_person_Bug_Reopen_data_sql(
        _date_start_time,
        _date_end_time,
        _tester
):
    return f"""
SELECT
        usr.realname as tester,
        count(if(bug.activatedCount > 0, 1, null)) as total
    FROM
        zt_bug as bug 
    LEFT JOIN
				zt_user as usr
    ON
        usr.account = bug.openedBy
    WHERE
        bug.deleted='0'
{
    '''AND
	        bug.closedDate >= '%s' ''' % _date_start_time if _date_start_time else ''
}
{
    '''AND
	        bug.closedDate < '%s' ''' % _date_end_time if _date_end_time else ''
}
{
    f'''AND
            usr.realname in  {_tester}''' if _tester and len(_tester) > 1 
    else f'''
        AND
            usr.realname = "{_tester[0]}"
    ''' if _tester
    else 
        ''
}
    GROUP BY
				usr.account
"""



def construct_developer_bug_data_sql(
        _date_start_time,
        _date_end_time,
        _developer,
        _pageNum,
        _pageSize
):
    return f"""
SELECT
	if(usr.realname is NOT NULL, usr.realname, bug.resolvedBy)	                                            as developer,
	count(if(bug.resolution is not NULL, 1, 0)) 	                                                        as has_solved_num,
	if(bug.confirmed = 0, count(1), 0) 		                                                                as no_confirm_num,
	if(bug.resolution is NULL, count(1), 0) 	                                                            as no_solved_num,
	sum(bug.activatedCount) 		                                                                        as activate_count_num,
	sum(bug.activatedCount) / count(bug.resolvedBy) 		                                                as average_activate_num,
	if(bug.activatedCount = 1, count(1), 0) 			                                                    as activate_num_1,
	if(bug.activatedCount = 2, count(1), 0) 			                                                    as activate_num_2,
	if(bug.activatedCount = 3, count(1), 0) 			                                                    as activate_num_3,
	if(bug.activatedCount > 3, count(1), 0) 			                                                    as activate_num_3_plus,
	sum(if(atn.date is not NULL, HOUR(TIMEDIFF(atn.date, bug.openedBuild)), 0)) / count(bug.resolvedBy) 	as confirm_hours,
	sum(HOUR(TIMEDIFF(bug.resolvedDate, bug.openedDate))) / count(bug.resolvedBy) 		                    as solved_hours
FROM
	zt_bug as bug 
LEFT JOIN
	zt_action as atn 
ON
	atn.objectID=bug.id and atn.objectType = 'bug' and atn.action = 'bugconfirmed'
LEFT JOIN
	zt_user as usr 
ON
	usr.account = bug.resolvedBy
WHERE
	bug.deleted = '0'
AND
	usr.deleted = '0'
{
    '''AND
            usr.realname = '%s' ''' % _developer if _developer else ''
}
{
    '''AND
            bug.closedDate >= '%s' ''' % _date_start_time if _date_start_time else ''
}
{
    '''AND
            bug.closedDate < '%s' ''' % _date_end_time if _date_end_time else ''
}
GROUP BY
	bug.resolvedBy
Limit {_pageNum * _pageSize}, {_pageSize}
"""



def construct_developer_all_sql(
        _date_start_time,
        _date_end_time,
        _developer,
):
    if _developer and isinstance(_developer, (tuple, list)):
        return f"select {len(_developer)} as developer_num"
    elif _developer:
        return  "select 1 as developer_num"
    else:
        return f"""
SELECT
    count(distinct bug.resolvedBy) as developer_num
FROM
	zt_bug as bug
LEFT JOIN
	zt_user as usr 
ON
	usr.account = bug.resolvedBy
where 
    usr.deleted = '0'
{
    '''AND
	        bug.closedDate >= '%s' ''' % _date_start_time if _date_start_time else ''
}
{
    '''AND
	        bug.closedDate < '%s' ''' % _date_end_time if _date_end_time else ''
}
"""









